﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetChildTierData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetChildTierData]
GO
CREATE PROCEDURE [dbo].[sp_GetChildTierData] (
	@ParentKey  [uniqueidentifier],
	@CultureInfo [nvarchar](20)
)
AS
BEGIN
	DECLARE @sqlExp AS NVARCHAR(MAX);
	SET @sqlExp = 'SELECT [Key],[ParentKey],[TierValue],[Name],[PostCode],[TelCode],[CountryKey],[CultureInfo],[LastUpdatedStamp] FROM [dbo].[view_GeographyTier]';
	IF(@ParentKey IS NULL)
		SET @sqlExp = @sqlExp + ' WHERE [CountryKey] = [Key]';
	ELSE
		SET @sqlExp = @sqlExp + ' WHERE [CountryKey] <> [Key] AND [ParentKey]=''' + CONVERT(NVARCHAR(MAX), @ParentKey) + '''';

	IF(@CultureInfo IS NOT NULL)
		SET @sqlExp = @sqlExp + ' AND [CultureInfo]=''' + @CultureInfo + '''';

	EXEC sp_executesql @sqlExp;
END

GO


